package net.huashitong.supervision.task.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.core.filter.Paging;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.supervision.task.dataBean.TaskUnitBean;
import net.huashitong.supervision.task.entity.TaskUnit;
import net.huashitong.supervision.task.filter.TaskUnitFilter;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: ling
 * Date: 2019/8/6
 * Time: 17:56
 * To change this template use File | Settings | File Templates.
 * Description:
 */
@Repository
public class TaskUnitDao extends StringDao<TaskUnit> {

    public List<TaskUnit> getBySubIdNoPage(String taskSubId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。
        query.append(" and taskSubId =:taskSubId").setParam("taskSubId", taskSubId);
        return query.listResult();
    }

    public List<TaskUnit> getByMainIdNoPage(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。
        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        return query.listResult();
    }

    public int getCountByType(String taskMainId, String type) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。

        if (StringUtils.isNotBlank(taskMainId)) {
            query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        }
        if (StringUtils.isNotBlank(type)) {
            query.append(" and type =:type").setParam("type", type);
        }
        return query.count();
    }

    public int getCountByStatus(String taskMainId, String status) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。

        if (StringUtils.isNotBlank(taskMainId)) {
            query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        }
        if (StringUtils.isNotBlank(status)) {
            query.append(" and status =:status").setParam("status", status);
        }
        return query.count();
    }

    public void batchDeleteIds(List<String> ids) {
        StringBuffer hql = (new StringBuffer("delete from TaskUnit")).append(" obj where obj.taskMainId in(:taskMainId)");
        Query query = this.getSession().createQuery(hql.toString());
        query.setParameterList("taskMainId", ids);
        query.executeUpdate();
    }

    public List<TaskUnit> queryLists(TaskUnitFilter filter){
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");
        if (StringUtils.isNotBlank(filter.getType())) {
            if("1".equals(filter.getType()))//1 待反馈，待反馈 包括 待反馈
            {
                query.append(" and (  type ='1' or type='5' or type='7'  )" );

            }else if("2".equals(filter.getType())){ //2已反馈

                query.append(" and ( type ='2' or type='4' or type='8'  or type='9' )" );
            }else  if("0".equals(filter.getType())){//未签收

                query.append(" and ( type ='0' or type='3' or type='6' )" );
            }
            else {

                query.append(" and type =:type").setParam("type",filter.getType());
            }

           // query.append(" and type =:type").setParam("type", filter.getType());
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            query.append(" and unitType =:unitType").setParam("unitType", filter.getUnitType());
        }
        if (StringUtils.isNotBlank(filter.getTaskSubId())) {
            query.append(" and taskSubId =:taskSubId").setParam("taskSubId", filter.getTaskSubId());
        }
        if (StringUtils.isNotBlank(filter.getTaskMainId())) {
            query.append(" and taskMainId=:taskMainId").setParam("taskMainId", filter.getTaskMainId());
        }
        if (StringUtils.isNotBlank(filter.getDeptId())&&( StringUtils.isBlank(filter.getUserId()) && StringUtils.isBlank(filter.getCreateUser()))  ) {
            query.append(" and deptId=:deptId").setParam("deptId", filter.getDeptId());
        }
        if (StringUtils.isNotBlank(filter.getCreateUser())&&StringUtils.isBlank(filter.getUserId())) {
            query.append(" and createUser=:createUser").setParam("createUser", filter.getCreateUser());
        }
        if (StringUtils.isNotBlank(filter.getUserId())&&StringUtils.isBlank(filter.getCreateUser())) {
            query.append(" and  userId =:userId ").setParam("userId", filter.getUserId());
        }
        if(StringUtils.isNotBlank(filter.getCreateUser())&&StringUtils.isNotBlank(filter.getUserId()))
        {
            query.append(" and ( createUser=:createUser").setParam("createUser", filter.getCreateUser())
            .append(" or  userId =:userId ").setParam("userId", filter.getUserId())
            .append(" or  deptId=:deptId  )").setParam("deptId", filter.getDeptId());

        }

        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            query.append(" and (taskMainName like:taskMainName").setParam("taskMainName", StringUtils.getLikewords(filter.getTaskMainName()));
        }
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            query.append(" or taskSubName like:taskSubName )").setParam("taskSubName", StringUtils.getLikewords(filter.getTaskSubName()));
        }
        if(filter.getImp()==1){

            query.append(" and imp=1");
        }else {
            query.append(" and (imp =0 or imp =2 )");
        }
        query.setOrder("updDate",GenericQuery.DESC);
        return  query.listResult(filter);

    }

    public List<TaskUnit> queryFilter(TaskUnitFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。

        if (StringUtils.isNotBlank(filter.getTaskSubId())) {
            query.append(" and taskSubId =:taskSubId").setParam("taskSubId", filter.getTaskSubId());
        }
        if (StringUtils.isNotBlank(filter.getTaskMainId())) {
            query.append(" and taskMainId=:taskMainId").setParam("taskMainId", filter.getTaskMainId());
        }
        if (StringUtils.isNotBlank(filter.getDeptId())) {
            query.append(" and deptId=:deptId").setParam("deptId", filter.getDeptId());
        }
        if (StringUtils.isNotBlank(filter.getUserId())) {
            query.append(" and createUser=:userId").setParam("userId", filter.getUserId());
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            query.append(" and (taskMainName like:taskMainName").setParam("taskMainName", StringUtils.getLikewords(filter.getTaskMainName()));
        }
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            query.append(" or taskSubName like:taskSubName )").setParam("taskSubName", StringUtils.getLikewords(filter.getTaskSubName()));
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            query.append(" and timeLimit >= :startTime").setParam("startTime", filter.getStartTime());
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            query.append(" and timeLimit <= :endTime").setParam("endTime", filter.getEndTime());
        }
        if (StringUtils.isNotBlank(filter.getDeptName())) {
            query.append(" and deptName like:deptName").setParam("deptName", StringUtils.getLikewords(filter.getDeptName()));
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            query.append(" and type =:type").setParam("type", filter.getType());
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            query.append(" and unitType =:unitType").setParam("unitType", filter.getUnitType());
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
//            String[] status = {filter.getStatus(), "10"};
//            query.append(" and status in(:status)").setParamList("status", status);
            query.append(" and status =:status").setParam("status", filter.getStatus());
        } else {
            String[] status = {"2", "10"};
            query.append(" and status in(:status)").setParamList("status", status);
        }
        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("sysId");
                filter.getPaging().setSortVal("desc");
            }
        }else{
            Paging paging = new Paging(0);
            paging.setSortCol("sysId");
            paging.setSortVal("desc");
            filter.setPaging(paging);
        }
        return query.listResult(filter);
    }

    /**
     * 待办任务（待签收，待反馈，重办）
     *
     * @param filter
     * @return
     */
    public List<TaskUnit> unDoneTask(TaskUnitFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and deptId =:deptId").setParam("deptId", filter.getDeptId());
        query.append(" and del is null");//只查询删除标识为空的记录。

        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            query.append(" and (taskSubName like:taskSubName").setParam("taskSubName", StringUtils.getLikewords(filter.getTaskSubName()));
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            query.append(" or taskMainName like:taskMainName )").setParam("taskMainName", StringUtils.getLikewords(filter.getTaskMainName()));
        }
         if (StringUtils.isNotBlank(filter.getCategoryId())) {
            query.append(" and categoryId =:categoryId ").setParam("categoryId",(filter.getCategoryId()));
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            query.append(" and timeLimit >= :startTime").setParam("startTime", filter.getStartTime());
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            query.append(" and timeLimit <= :endTime").setParam("endTime", filter.getEndTime());
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            query.append(" and unitType =:unitType").setParam("unitType", filter.getUnitType());
        }
        if (StringUtils.isNotBlank(filter.getUserId())) {
            query.append(" and createUser =:createUser").setParam("createUser", filter.getUserId());
        }

        if (StringUtils.isNotBlank(filter.getStatus())) {
            String[] status = {filter.getStatus(), "10"};
            query.append(" and status in(:status)").setParamList("status", status);
//            query.append(" and status =:status").setParam("status", filter.getStatus());
        } else {
            String[] status = {"2", "10"};
            query.append(" and status in(:status)").setParamList("status", status);
        }
        query.append(" and state =:state").setParam("state", "0");
       /* query.append(" and (type =:type").setParam("type", "0");
        query.append(" or type =:type1").setParam("type1", "1");
        query.append(" or type =:type2").setParam("type2", "3");
        query.append(" or innerBack =:innerBack ) ").setParam("innerBack", "1");*/
        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("sysId");
                filter.getPaging().setSortVal("desc");
            }
        }else{
            Paging paging = new Paging(0);
            paging.setSortCol("sysId");
            paging.setSortVal("desc");
            filter.setPaging(paging);
        }
        return query.listResult(filter);
    }


    public List<TaskUnit> getByMainId(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。

        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        return query.listResult();
    }
    public List<TaskUnit> getByMainIdDel(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del =:del ").setParam("del","0" );

        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        return query.listResult();
    }

    public List<TaskUnit> getBySubId(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del =:del ").setParam("del","0" );

        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        return query.listResult();
    }

    public List<TaskUnit> getBySubId2(String tasksubId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null ");

        query.append(" and taskSubId =:taskSubId").setParam("taskSubId", tasksubId);
        return query.listResult();
    }


    /**
     * 个人完成
     *
     * @param filter
     * @return
     */
    public List<TaskUnit> getDoneUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.sys_id sysId,t1.time_round timeRound,t1.task_main_name taskMainName,t1.task_main_id taskMainId ,t1.task_sub_id taskSubId,t1.task_sub_name taskSubName,t1.dept_id deptId ,t1.user_id userId ,t1.dept_name deptName,t1.time_limit timeLimit,t2.status as type ,t1.comment as comment,t1.score as score," +
                " t1.overdue_day overdueDay,t1.unit_type unitType ,t1.status as status from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t2.status = '" + filter.getType() + "'");
        }
        sql.append(" and t2.status = '4'");
        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

        // 设置查询总条数,用于分页
        if (ObjectUtils.isNotBlank(filter.getPaging())) {

            int resultSize = this.countDoneUser(filter);
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }

            SQLQuery query = getSession().createSQLQuery(sql.toString());

            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            List<TaskUnit> list = query.list();
            return list;
        } else {
            sql.append(" order by t1.sys_id desc ");

            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            List<TaskUnit> list = query.list();
            return list;
        }
    }

    private int countDoneUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(t1.sys_id) from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and ( t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t2.status = '" + filter.getType() + "'");
        }
        sql.append(" and t2.status = '4'");
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }

    /**
     * 单位个人待办任务
     *
     * @param filter
     * @return
     */
    public List<TaskUnit> getUnDoneUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.sys_id sysId,t1.time_round timeRound,t1.task_main_name taskMainName,t1.task_main_id taskMainId ,t1.task_sub_id taskSubId,t1.task_sub_name taskSubName,t1.dept_id deptId ,t1.user_id userId ,t1.dept_name deptName,t1.time_limit timeLimit,t2.status as type ,t1.comment as comment,t1.score as score," +
                " t1.overdue_day overdueDay,t1.unit_type unitType ,t1.status as status from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t2.status = '" + filter.getType() + "'");
        }
//        sql.append(" and t2.status in ('0','3')");
        sql.append(" and t2.state ='0' ");
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        // 设置查询总条数,用于分页
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            int resultSize = this.countUnDone(filter);
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }

            SQLQuery query = getSession().createSQLQuery(sql.toString());

            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            List<TaskUnit> list = query.list();
            return list;
        } else {
            sql.append(" order by t1.sys_id desc ");

            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            List<TaskUnit> list = query.list();
            return list;
        }

    }

    private int countUnDone(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(t1.sys_id) from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t2.status = '" + filter.getType() + "'");
        }
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

//        sql.append(" and t2.status in ('0','3')");
        sql.append(" and t2.state ='0' ");

        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }

    /**
     * 单位在办
     *
     * @param filter
     * @return
     */
    public List<TaskUnit> getNowUnit(TaskUnitFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and deptId =:deptId").setParam("deptId", filter.getDeptId());
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            query.append(" and (taskSubName like:taskSubName").setParam("taskSubName", StringUtils.getLikewords(filter.getTaskSubName()));
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            query.append(" or taskMainName like:taskMainName)").setParam("taskMainName", StringUtils.getLikewords(filter.getTaskMainName()));
        }
        if (StringUtils.isNotBlank(filter.getCategoryId())) {
            query.append(" and categoryId =:categoryId ").setParam("categoryId",(filter.getCategoryId()));
        }
        if (StringUtils.isNotBlank(filter.getUserId())) {
            query.append(" and createUser=:userId").setParam("userId", filter.getUserId());
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            query.append(" and timeLimit >= :startTime").setParam("startTime", filter.getStartTime());
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            query.append(" and timeLimit <= :endTime").setParam("endTime", filter.getEndTime());
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            query.append(" and unitType =:unitType").setParam("unitType", filter.getUnitType());
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            String[] status = {filter.getStatus(), "10"};
            query.append(" and status in(:status)").setParamList("status", status);
//            query.append(" and status =:status").setParam("status", filter.getStatus());
        } else {
            String[] status = {"2", "10"};
            query.append(" and status in(:status)").setParamList("status", status);

        }
        query.append(" and del is null ");//只查询删除标识为空的记录。

        query.append(" and type !=:type").setParam("type", "4");
        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("sysId");
                filter.getPaging().setSortVal("desc");
            }
        }else{
            Paging paging = new Paging(0);
            paging.setSortVal("desc");
            paging.setSortCol("sysId");
            filter.setPaging(paging);
        }
        return query.listResult(filter);
    }

    /**
     * 单位个人在办任务
     *
     * @param filter
     * @return
     */
    public List<TaskUnit> getNowUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.sys_id sysId,t1.time_round timeRound,t1.task_main_name taskMainName,t1.task_main_id taskMainId ,t1.task_sub_id taskSubId,t1.task_sub_name taskSubName,t1.dept_id deptId ,t1.user_id userId ,t1.dept_name deptName,t1.time_limit timeLimit,t2.status as type ,t1.comment as comment,t1.score as score," +
                " t1.overdue_day overdueDay,t1.unit_type unitType ,t1.status as status from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t2.status = '" + filter.getType() + "'");
        }
        sql.append(" and t2.status != '4'");
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        // 设置查询总条数,用于分页
        int resultSize = this.countNowUser(filter);

        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }
            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            return query.list();
        } else {
            sql.append(" order by t1.sys_id desc ");
            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            return query.list();
        }
    }

    private int countNowUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(t1.sys_id) from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t2.status = '" + filter.getType() + "'");
        }
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。
        sql.append(" and t2.status != '4'");
        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }


    /**
     * 单位
     * 全部任务
     *
     * @param filter
     * @return
     */
    public List<TaskUnit> getAllUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.sys_id sysId,t1.time_round timeRound,t1.task_main_name taskMainName,t1.task_main_id taskMainId ,t1.task_sub_id taskSubId,t1.task_sub_name taskSubName,t1.dept_id deptId ,t1.user_id userId ,t1.dept_name deptName,t1.time_limit timeLimit,t2.status as type ,t1.comment as comment,t1.score as score," +
                " t1.overdue_day overdueDay,t1.unit_type unitType ,t1.status as status from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t2.status ='" + filter.getType() + "'");
        }
        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。


        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            // 设置查询总条数,用于分页
            int resultSize = this.countAllUser(filter);
            filter.getPaging().setRecords(resultSize);
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("t1.sys_id");
                filter.getPaging().setSortVal("desc");
            }
            sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            List<TaskUnit> list = query.list();
            return list;
        }else {
            sql.append(" order by t1.sys_id desc");
            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setResultTransformer(Transformers.aliasToBean(TaskUnit.class));
            List<TaskUnit> list = query.list();
            return list;
        }
    }

    private int countAllUser(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(t1.sys_id) from t_task_unit t1 ,t_task_user t2 where t1.sys_id = t2.task_unit_id ");
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            sql.append(" and (t1.task_sub_name like '%" + filter.getTaskSubName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            sql.append(" or t1.task_main_name like '%" + filter.getTaskMainName() + "%')");
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            sql.append(" and t1.time_limit >= '"+ filter.getStartTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            sql.append(" and t1.time_limit <= '"+ filter.getEndTime()+"'");
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            sql.append(" and t1.unit_type ='" + filter.getUnitType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t2.status ='" + filter.getType() + "'");
        }
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

        sql.append(" and  t2.unit_user_id ='" + filter.getUserId() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }

    public List<TaskUnit> getBySubIdAndUnitType(String taskSubId, String unitType, String timeRound) {
        GenericQuery obj = createQuery("obj");
        obj.append(" and del is null ");
        obj.append(" and taskSubId =:taskSubId").setParam("taskSubId", taskSubId);
        obj.append(" and unitType =:unitType").setParam("unitType", unitType);
        obj.append(" and timeRound =:timeRound").setParam("timeRound", timeRound);
        return obj.listResult();

    }


    public int getCountBySubDept(String subId, String deptId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null ");
        query.append(" and taskSubId =:taskSubId").setParam("taskSubId", subId);
        query.append(" and deptId =:deptId").setParam("deptId", deptId);
        return query.count();
    }

    public List<TaskUnit> getBySubDeptIdAndTimeRound(String sysId, String deptId, int i) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null ");

        query.append(" and taskSubId =:taskSubId").setParam("taskSubId", sysId);
        query.append(" and deptId =:deptId").setParam("deptId", deptId);
        query.append(" and timeRound =:timeRound").setParam("timeRound", i);

        return null;
    }

    public List<TaskUnit> getBySubIdNew(TaskUnitFilter filter) {
        int maxRoundBySubId = this.getMaxRoundBySubId(filter.getTaskSubId());
        GenericQuery query = createQuery("obj");
        query.append(" and del is null ");
        if (StringUtils.isNotBlank(filter.getSearch())){
            query.append(" and taskSubName like :taskSubName").setParam("taskSubName", StringUtils.getLikewords(filter.getTaskSubName()));
        }
        if(StringUtils.isNotBlank(filter.getUnitType()))
        {
            query.append(" and unitType = :unitType").setParam("unitType",filter.getUnitType());
        }
        query.append(" and taskSubId = :taskSubId").setParam("taskSubId", filter.getTaskSubId());
        query.append(" and timeRound = :timeRound").setParam("timeRound", maxRoundBySubId + "");
        return query.listResult(filter);
    }

    public int getMaxRoundBySubId(String subSysId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select max(time_round+0) from t_task_unit where del is null and task_sub_id ='" + subSysId + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        if (query.uniqueResult() == null) {
            return 0;
        } else {
            return ((Number) query.uniqueResult()).intValue();
        }
    }

    public List<TaskUnit> getBySubIdUnDone(TaskUnitFilter filter) {
        int maxRoundBySubId = this.getMaxRoundBySubId(filter.getTaskSubId());
        if (maxRoundBySubId <= 1) {
            return null;
        } else {
            List<String> round = new ArrayList<>();
            for (int i = 1; i < maxRoundBySubId; i++) {
                round.add(i + "");
            }
            GenericQuery query = createQuery("obj");
            query.append(" and del is null ");
            if (StringUtils.isNotBlank(filter.getTaskSubName())) {
                query.append(" and subName like :subName").setParam("subName", StringUtils.getLikewords(filter.getTaskSubName()));
            }
            query.append(" and taskSubId = :taskSubId").setParam("taskSubId", filter.getTaskSubId());
            query.append(" and type != :type").setParam("type", "4");
            query.append(" and timeRound in :timeRound").setParamList("timeRound", round);
            return query.listResult(filter);
        }
    }

    public List<TaskUnit> getByOldTaskUnit(String taskSubId, String deptId, int countBySubDept) {
        List<String> round = new ArrayList<>();
        for (int i = 1; i < countBySubDept; i++) {
            round.add(i + "");
        }
        GenericQuery query = createQuery("obj");
        query.append(" and del is null ");

        query.append(" and taskSubId = :taskSubId").setParam("taskSubId", taskSubId);
        query.append(" and deptId = :deptId").setParam("deptId", deptId);
//        query.append(" and type != :type").setParam("type", "4");
        query.append(" and timeRound in :timeRound").setParamList("timeRound", round);
        return query.listResult();
    }

    public List<TaskUnitBean> queryByCategory(TaskUnitFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select t1.sys_id sysId,t1.time_round timeRound,t1.task_main_name taskMainName,t1.task_main_id taskMainId ,t1.task_sub_id taskSubId,t1.task_sub_name taskSubName,t1.dept_id deptId ,t1.user_id userId ,t1.dept_name deptName,t1.time_limit timeLimit,t1.type as type ,t1.comment as comment,t1.score as score," +
                " t1.overdue_day overdueDay,t1.unit_type unitType ,t1.status as unitStatus,t2.category_id as categoryId from t_task_unit t1 ,t_task_main t2 where t2.sys_id = t1.task_main_id ");
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and t1.type = '" + filter.getType()+"'");
        }
        if (StringUtils.isNotBlank(filter.getCategoryId())){
            sql.append(" and t2.category_id != '" +filter.getCategoryId()+"'");
        }
        if (StringUtils.isNotBlank(filter.getDeptId())){
            sql.append(" and t1.dept_id = '"+filter.getDeptId()+"'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
//            String[] status = {filter.getStatus(), "10"};
//            query.append(" and status in(:status)").setParamList("status", status);
            sql.append(" and t1.status = "+filter.getStatus());
        } else {
            String[] status = {"2", "10"};
            sql.append(" and t1.status in ('2','10')");
        }
        sql.append(" and t2.del is null and t1.del is null ");//只查询删除标识为空的记录。

        SQLQuery query = getSession().createSQLQuery(sql.toString());

    /*    query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
        query.setMaxResults(filter.getPaging().getPageSize());*/
        query.setResultTransformer(Transformers.aliasToBean(TaskUnitBean.class));
        List<TaskUnitBean> list = query.list();
        return list;
    }

    public List<TaskUnit> queryStatistics(TaskUnitFilter filter) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。

        if (StringUtils.isNotBlank(filter.getTaskSubId())) {
            query.append(" and taskSubId =:taskSubId").setParam("taskSubId", filter.getTaskSubId());
        }
        if (StringUtils.isNotBlank(filter.getTaskMainId())) {
            query.append(" and taskMainId=:taskMainId").setParam("taskMainId", filter.getTaskMainId());
        }
        if (StringUtils.isNotBlank(filter.getDeptId())) {
            query.append(" and deptId=:deptId").setParam("deptId", filter.getDeptId());
        }
        if (StringUtils.isNotBlank(filter.getTaskMainName())) {
            query.append(" and (taskMainName like:taskMainName").setParam("taskMainName", StringUtils.getLikewords(filter.getTaskMainName()));
        }
        if (StringUtils.isNotBlank(filter.getTaskSubName())) {
            query.append(" or taskSubName like:taskSubName )").setParam("taskSubName", StringUtils.getLikewords(filter.getTaskSubName()));
        }
        if (StringUtils.isNotBlank(filter.getStartTime())) {
            query.append(" and timeLimit >= :startTime").setParam("startTime", filter.getStartTime());
        }
        if (StringUtils.isNotBlank(filter.getEndTime())) {
            query.append(" and timeLimit <= :endTime").setParam("endTime", filter.getEndTime());
        }
        if (StringUtils.isNotBlank(filter.getDeptName())) {
            query.append(" and deptName like:deptName").setParam("deptName", StringUtils.getLikewords(filter.getDeptName()));
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            query.append(" and type =:type").setParam("type", filter.getType());
        }
        if (StringUtils.isNotBlank(filter.getUnitType())) {
            query.append(" and unitType =:unitType").setParam("unitType", filter.getUnitType());
        }
        if (StringUtils.isNotBlank(filter.getOverdueDay())) {
            query.append(" and overdueDay is not null " );
        }
        if (StringUtils.isNotBlank(filter.getBackNum())) {
            query.append(" and backNum is not null " );
        }
        //查询的创建时间.条件只会在月,当数据创建时间与查询月份相同时
        if (StringUtils.isNotBlank(filter.getCreateDate())) {
            query.append(" and createDate like:createDate").setParam("createDate",StringUtils.getLikewords( filter.getCreateDate()));
        }
        if (StringUtils.isNotBlank(filter.getUpdateDate())) {
            query.append(" and updDate like:updDate").setParam("updDate",StringUtils.getLikewords( filter.getUpdateDate()));
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
//            String[] status = {filter.getStatus(), "10"};
//            query.append(" and status in(:status)").setParamList("status", status);
            query.append(" and status =:status").setParam("status", filter.getStatus());
        } else {
            String[] status = {"2", "10"};
            query.append(" and status in(:status)").setParamList("status", status);
        }
        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("sysId");
                filter.getPaging().setSortVal("desc");
            }
        }else{
            Paging paging = new Paging(0);
            paging.setSortCol("sysId");
            paging.setSortVal("desc");
            filter.setPaging(paging);
        }
        return query.listResult(filter);
    }

    /**
     * 近7天生成的督查单位任务
     * @param taskUnitFilter
     * @return
     */
    public List<TaskUnit> queryDateFilter(TaskUnitFilter taskUnitFilter) {
        GenericQuery obj = createQuery("obj");
        obj.append(" and del is null");
        obj.append(" and createDate>:createDate").setParam("createDate",taskUnitFilter.getCreateDate());
        return obj.listResult(taskUnitFilter);
    }

    public List<TaskUnit> getByMainIdOneRound(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");//只查询删除标识为空的记录。
        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        query.append(" and timeRound =:timeRound").setParam("timeRound", "1");
        return query.listResult();
    }

    /*public Integer getMaxValue() {
        GenericQuery query = create(" select max(timeRound) from  TaskUnit t");

        int i = query.getMaxResults();
        return i;
    }*/

}
